"""
Case Type   : 极致RTO支持备机可读
Case Name   : 传统集群不带CM，开启极致RTO, switchover场景下验证备机可读
Create At   : 2025/3
Owner       : haomeng
Description :
    1、集群内开启极致RTO特性，重启使其生效
    2、创建行存段页式range分区表，插入数据
    3、备机查看表数据
    4、在准备升为主节点的节点执行switchover操作，查看集群状态
    5、在新的备节点(原主节点)内查看段页式表数据
    6、在新主节点内创建行存段页式表，插入数据
    7、在新主节点内查看段页式表数据
    8、清理环境，恢复环境初始状态
Expect      :
    1、设置成功，重启成功
    2、创建成功，插入数据成功
    3、查看成功
    4、切换成功，主备节点切换，集群状态正常
    5、查看成功
    6、创建成功，插入数据成功
    7、查看成功
    8、清理成功，恢复环境成功
History     :
"""

import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonSH import RestartDbCluster
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class RTOStandbyRead(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.sta_sh = CommonSH('Standby1DbUser')
        self.user_node = Node(node='PrimaryDbUser')
        self.sta_node = Node(node='Standby1DbUser')
        self.constant = Constant()

        self.param_list = ['recovery_parse_workers', 'recovery_redo_workers']
        self.value_list = ['4', '4']
        self.default_value = []
        self.table = 't_rto_002'
        self.table_new = 't_rto_002_new'

    def test_rto_standby_read(self):
        text = '-----step1:集群内开启极致RTO特性，重启使其生效;  expect:设置成功，重启成功-----'
        self.log.info(text)

        for param in self.param_list:
            value = self.pri_sh.executDbSql(f'show {param}')
            self.default_value.append(value.splitlines()[-2].strip())
        self.log.info(self.default_value)

        for param in self.param_list:
            set_param = self.pri_sh.executeGsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f'{param} = {self.value_list[self.param_list.index(param)]}')
            self.log.info(set_param)
            self.assertTrue(set_param, '参数设置失败')

        self.log.info('---重启集群使参数生效---')
        restart = self.pri_sh.om_restart()
        self.log.info(restart)
        self.assertTrue(restart, '集群重启失败')

        self.log.info('---查看参数值修改是否成功---')
        for param in self.param_list:
            value = self.pri_sh.executDbSql(f'show {param};')
            self.assertEqual(value.splitlines()[-2].strip(),
                             self.value_list[self.param_list.index(param)], '参数未修改成功')

        text = '-----step2:创建行存段页式range分区表，插入数据  expect:创建成功，插入数据成功-----'
        self.log.info(text)

        sql1 = f"drop table if exists {self.table};" \
               f"create table {self.table}(id int, name varchar(200), age int, tel int) with(segment=on) " \
               f"partition by range(tel) " \
               f"(partition p1 values less than(5000), partition p2 values less than(10000), " \
               f"partition p3 values less than(15000), partition p4 values less than(20000), " \
               f"partition p5 values less than(25000), partition p6 values less than(30000), " \
               f"partition p7 values less than(40000), partition p8 values less than(50000)); " \
               f"declare    " \
               f"    i integer;" \
               f"begin" \
               f"    for i in 1..100000 loop" \
               f"    insert into {self.table} values(random()*100,'name_'||random()*10,random()*100,random()*10000);" \
               f"    end loop;" \
               f"end;"
        self.log.info(sql1)
        res1 = self.pri_sh.executDbSql(sql1)
        self.log.info(res1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res1, '建表失败')
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res1, '匿名块执行失败，插入数据失败')

        text = '-----step3:备节点查看表数据  expect:查看成功-----'
        self.log.info(text)

        self.log.info('---等待备节点数据同步---')
        flag = self.sta_sh.check_data_consistency()
        self.assertTrue(flag, '数据同步失败')

        sql2 = f"select count(*) from {self.table};"
        self.log.info(sql2)
        select_res = self.sta_sh.executDbSql(sql2)
        self.log.info(select_res)
        self.assertIn('100000', select_res.splitlines()[-2].strip(), '查询结果不一致')

        text = '-----step4:在准备升为主节点的节点执行switchover操作，查看集群状态  ' \
               'expect:切换成功，主备节点切换，集群状态正常-----'
        self.log.info(text)

        switch = self.sta_sh.executeGsctl('switchover', self.constant.SWITCH_SUCCESS_MSG,
                                          param=f'-D {macro.DB_INSTANCE_PATH}')
        self.log.info(switch)
        self.assertTrue(switch, '拉起失败')

        status = self.pri_sh.getDbClusterStatus('status')
        self.log.info(status)
        self.assertTrue(status)

        text = '-----step5:在新的备节点(原主节点)内查看段页式表数据  expect:查看成功-----'
        self.log.info(text)

        select_res = self.pri_sh.executDbSql(sql2)
        self.log.info(select_res)
        self.assertIn('100000', select_res.splitlines()[-2].strip(), '查询结果不一致')

        text = '-----step6:在新主节点内创建行存段页式表，插入数据  expect:创建成功，插入数据成功-----'
        self.log.info(text)
        sql3 = f"create table {self.table_new}(id int, name varchar(200), " \
               f"age int, tel int) with(segment=on);" \
               f"declare" \
               f"    i integer;" \
               f"begin" \
               f"    for i in 1..100000 loop" \
               f"    insert into {self.table_new} values(random()*100," \
               f"    'name_'||random()*10,random()*100,random()*10000);" \
               f"    end loop;" \
               f"end;"
        self.log.info(sql3)

        res3 = self.sta_sh.executDbSql(sql3)
        self.log.info(res3)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res3, '建表失败')
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res3, '匿名块执行失败，插入数据失败')

        text = '-----step7:在新备节点内查看段页式表数据  expect:查看成功-----'
        self.log.info(text)

        self.log.info('---等待备节点数据同步---')
        flag = self.pri_sh.check_data_consistency()
        self.assertTrue(flag, '数据同步失败')

        sql4 = f"select count(*) from {self.table_new};"
        self.log.info(sql4)
        select_res = self.pri_sh.executDbSql(sql4)
        self.log.info(select_res)
        self.assertIn('100000', select_res.splitlines()[-2].strip(), '查询结果不一致')

        sta_res = self.sta_sh.executDbSql(sql4)
        self.log.info(sta_res)
        self.assertEqual(select_res.splitlines()[-2], sta_res.splitlines()[-2], '查询结果不一致')

    def tearDown(self):
        text = '-----step8:清理环境，恢复环境初始状态  expect:清理成功，恢复环境成功-----'
        self.log.info(text)

        self.log.info('-----恢复原始主备状态-----')
        switch = self.pri_sh.executeGsctl('switchover', self.constant.SWITCH_SUCCESS_MSG,
                                          f'-D {macro.DB_INSTANCE_PATH}')
        self.log.info(switch)

        status = self.pri_sh.getDbClusterStatus('status')
        self.log.info(status)

        drop = f"drop table {self.table} cascade;" \
               f"drop table {self.table_new} cascade;"
        self.log.info(drop)
        drop_res = self.pri_sh.executDbSql(drop)
        self.log.info(drop_res)

        for param in self.param_list:
            reset_param = self.pri_sh.executeGsguc('set',
                                                    self.constant.GSGUC_SUCCESS_MSG,
                                                    f'{param} = {self.default_value[self.param_list.index(param)]}')
            self.log.info(reset_param)

        restart = self.pri_sh.om_restart(False)
        self.log.info(restart)

        for param in self.param_list:
            reset_value = self.pri_sh.executDbSql(f'show {param};')
            self.log.info(reset_value)
            self.assertEqual(reset_value.splitlines()[-2].strip(),
                             self.default_value[self.param_list.index(param)], '参数恢复失败')
        self.assertEqual(2, drop_res.count(self.constant.DROP_TABLE_SUCCESS), '删除表失败')
        self.assertTrue(status)
        self.log.info(f'----{os.path.basename(__file__)} end----')
